 package fu.abc.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import fu.abc.model.Account;


public class AccountDAO {
	
	Connection connect = null;
	PreparedStatement pre = null;
	ResultSet result = null;
	Statement state = null;
	
//	login
	
	public String login(String username, String password) throws SQLException{
		
			String role = null;
			try {
//				connect to DB
				connect = UtilConnection.getConnection();
				if(connect!=null){
					String query = "SELECT role from ACCOUNT WHERE username='"+username+"' AND password='"+password+"' AND status=1 ";

					Statement stm = connect.createStatement();
					ResultSet re = stm.executeQuery(query);
					while(re.next()){
//						get role from DB
						role = re.getString("role").trim();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
//			return role with right username and password
			return role;
		}
	
//	list account
	
	public Account getAccountbyUsername(String username){
			
		Account acc = new Account();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			
			String query = "SELECT idAcc FROM Account WHERE username='"+username+"'";

			Statement stm = connect.createStatement();
//			execute query
			result = stm.executeQuery(query);
			while(result.next()){
//				get id of account from DB
				acc.setIdAcc(result.getInt("idAcc"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return acc;
		
	}
	
//	list account by search
	 public List<Account> searchUser(String search, String role) {
		 
	        String query ="";
	        List<Account> list = new ArrayList<>();
	        
	        try {
//	        	state = (Statement) connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
	        	connect = UtilConnection.getConnection(); // connect to DB
	        	state = connect.createStatement();
//	        	list all
	            if (("".equals(search) || search==null) && ("".equals(role)  || role==null)) {
	            	query = "SELECT idacc,fullname,address,role,username,status FROM ACCOUNT";
//	            list by role	
	            }else if(("".equals(search) || search==null) && (!"".equals(role) || role!=null)){
	            	query = "SELECT idacc,fullname,address,role,username,status FROM ACCOUNT WHERE role LIKE '%"+role+"%'";
//	            list by text search
	            }else if((!"".equals(search) || search!=null) && ("".equals(role) || role==null)){
	            	query = "SELECT idacc,fullname,address,role,username,status FROM ACCOUNT WHERE rtrim(dbo.ufn_catdau([fullname])) like N'%"+search+"%' OR FREETEXT(fullname,'"+search+"')";
//	            list by text and role
	            }else if((!"".equals(search) || search!=null) && (!"".equals(role) || role!=null)){
	            	query = "SELECT idacc,fullname,address,role,username,status FROM ACCOUNT WHERE rtrim(dbo.ufn_catdau([fullname])) like N'%"+search+"%' OR FREETEXT(fullname,'"+search+"')"
	            			+ " AND role LIKE '%"+role+"%'";
	            }
//	            	execute query
	            	result = state.executeQuery(query);
	                while (result.next()) {
	                	Account acc = new Account();
	    				acc.setIdAcc(result.getInt("idacc"));
	    				acc.setFullname(result.getString("fullname"));
	    				acc.setAddress(result.getString("address"));
	    				acc.setRole(result.getString("role"));
	    				acc.setUsername(result.getString("username"));
	    				acc.setStatus(result.getInt("status"));
	    				list.add(acc);
	                }
	        } catch (SQLException ex) {

	        }
//	        return list account
	        return list;
	    }
	 
//	view account 
	
	 public Account getAccountInfor(int id) throws SQLException{
		
		Account acc = new Account();
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				String query = "SELECT * FROM ACCOUNT WHERE idacc='"+id+"'";
//				execute query
				pre = connect.prepareStatement(query);
				result = pre.executeQuery();
				
				while(result.next()){
//					get information from DB
					acc.setIdAcc(result.getInt("idacc"));
					acc.setUsername(result.getString("username"));
					acc.setAddress(result.getString("address"));
					acc.setFullname(result.getString("fullname"));
					acc.setEmail(result.getString("email"));
					acc.setPassword(result.getString("password"));
					acc.setDob(result.getString("dob"));
					acc.setCmt(result.getInt("cmt"));
					acc.setPhone(result.getString("phone"));
					acc.setRole(result.getString("role"));
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
				connect.close();
			}
		}
		
		return acc;		
	}

//	delete account

	public void delete(int id) throws SQLException{
		
		try {
			String query ="UPDATE ACCOUNT SET [status]=0 WHERE idacc = "+id+" ";
//			connect to DB
			connect = UtilConnection.getConnection();
			
			Statement stm = connect.createStatement();
			stm.execute(query);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connect!=null){
//				close connect
				connect.close();
			}
		}
	}
	
//	edit account
	
	 public void updateAccount(Account account){
		
		 String query = "UPDATE ACCOUNT SET fullname=?,dob=?,address=?,email=?,phone=?,password=?,role=? WHERE idacc=? AND status=1";
		try {
//			connect to DB
			connect = UtilConnection.getConnection();
			if(connect!=null){
				
				pre = connect.prepareStatement(query);
				pre.setString(1, account.getUsername());
				pre.setString(2, account.getDob());
				pre.setString(3, account.getAddress());
				pre.setString(4, account.getEmail());
				pre.setString(5, account.getPhone());
				pre.setString(6, account.getPassword());
				pre.setString(7, account.getRole());
				pre.setInt(8, account.getIdAcc());
//				update data
				pre.executeUpdate();
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if(connect!=null){
				try {
					connect.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

//	create account

	public boolean createAccount(Account account) throws SQLException{
		
//		connect to DB
		connect = UtilConnection.getConnection();
		if(connect!=null){
			try {
				
				String query = "INSERT INTO Account(username,address,fullname,email,password,dob,cmt,phone,role,[status]"
						+ ") VALUES(?,?,?,?,?,?,?,?,?,?)";
//				call query
				pre = connect.prepareStatement(query);
				
				pre.setString(1, account.getUsername());
				pre.setString(2, account.getAddress());
				pre.setString(3, account.getFullname());
				pre.setString(4, account.getEmail());
				pre.setString(5, account.getPassword());
				pre.setString(6, account.getDob());
				pre.setInt(7, account.getCmt());
				pre.setString(8, account.getPhone());
				pre.setString(9, account.getRole());
				pre.setInt(10, account.getStatus());
//				execute insert into DB
				pre.execute();
				return true;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}finally{
				if(connect!=null){
					connect.close();
				}
			}
		} else
			return false;
	}
	
	
}
